package banco.cmv;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import utilidades.FormatarData;
import banco.Banco;
import dominio.Cliente;
import dominio.Tipo_Planilha;
import dominio.cmv.Informativo_Fiscal;
import dominio.cmv.Layout;
import dominio.cmv.Saldo_Estoque;
import dominio.cmv.Total_Porc;
import fachada.cmv.FachadaLayout;

public class LayoutDAO implements FachadaLayout{

	@SuppressWarnings("finally")
	public void insertLayout(Layout l, Informativo_Fiscal info) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into custo_cmv values (?, ?, ?, ?, ?, ?, ?)");
	            stmt.setString(1, FormatarData.formatar(l.getData()));
	            stmt.setFloat(2, l.getValor());
	            stmt.setFloat(3, l.getCampo());
	            stmt.setFloat(4, l.getTotal());
	            stmt.setFloat(5, l.getCusto_p_lancar());
	            stmt.setFloat(6, l.getTotal_faturamento());
	            stmt.setInt(7, info.getId());
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }
	
	@SuppressWarnings("finally")
	public void insertSaldoEstoque(Saldo_Estoque se, Informativo_Fiscal info) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into cmv_saldo_estoque values (?, ?, ?, ?)");
	            stmt.setString(1, FormatarData.formatar(se.getData()));
	            stmt.setFloat(2, se.getAntes_lancar_custo());
	            stmt.setFloat(3, se.getDepois_lancar_custo());	            
	            stmt.setInt(4, info.getId());
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }
	
	@SuppressWarnings("finally")
	public void insertTotal_Porc(Total_Porc tp, Informativo_Fiscal info) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into total_porc_cmv values (?, ?, ?, ?, ?, ?, ?, ?)");
	            stmt.setFloat(1, tp.getTotal_venda());
	            stmt.setFloat(2, tp.getTotal_servico());
	            stmt.setFloat(3, tp.getPorc_servico());
	            stmt.setFloat(4, tp.getTotal_custo_p_lancar());
	            stmt.setFloat(5, tp.getPorc_total_cpl());
	            stmt.setFloat(6, tp.getSoma_total_faturamento());
	            stmt.setFloat(7, tp.getPorc_venda());
	            stmt.setInt(8, info.getId());
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }
	
	@SuppressWarnings("finally")
	public Collection selectLayout(Informativo_Fiscal info) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Collection<Layout> resultado = new ArrayList<Layout>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from custo_cmv where id_info_fiscal = ?");
	        stmt.setInt(1, info.getId());
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	Layout l = new Layout();
	        	l.setCampo(rs.getFloat("servico"));
	        	l.setData_string(rs.getString("data"));
	        	l.setCusto_p_lancar(rs.getFloat("custo_p_lancar"));
	        	l.setData(FormatarData.inserirData(rs.getString("data")));
	        	l.setTotal(rs.getFloat("total_venda"));
	        	l.setTotal_faturamento(rs.getFloat("total_faturamento"));
	        	l.setValor(rs.getFloat("valor_venda"));
	        	resultado.add(l);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return resultado;
	    }
	}
	
	@SuppressWarnings("finally")
	public Collection selectSaldo_Estoque(Informativo_Fiscal info) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Collection<Saldo_Estoque> res = new ArrayList<Saldo_Estoque>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from cmv_saldo_estoque where id_info_fiscal = ?");
	        stmt.setInt(1, info.getId());
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	Saldo_Estoque se = new Saldo_Estoque();
	        	se.setData_string(rs.getString("data"));
	        	se.setAntes_lancar_custo(rs.getFloat("antes_lancar_custo"));
	        	se.setDepois_lancar_custo(rs.getFloat("depois_lancar_custo"));
	        	res.add(se);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
	    }
	    
	}

	@SuppressWarnings("finally")
	public Total_Porc selectTotal_Porc(Informativo_Fiscal info) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Total_Porc total = new Total_Porc();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from total_porc_cmv where id_info_fiscal = ?");
	        stmt.setInt(1, info.getId());
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	total.setPorc_servico(rs.getFloat("porc_servico"));
	        	total.setPorc_total_cpl(rs.getFloat("porc_total_cpl"));
	        	total.setPorc_venda(rs.getFloat("porc_venda"));
	        	total.setSoma_total_faturamento(rs.getFloat("soma_total_faturamento"));
	        	total.setTotal_custo_p_lancar(rs.getFloat("total_custo_p_lancar"));
	        	total.setTotal_servico(rs.getFloat("total_servico"));
	        	total.setTotal_venda(rs.getFloat("total_venda"));
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return total;
	    }
	    
	}
}
